<?php

/**
 * Capital Direct Lending (http://www.philbsd.com/cdl)
 *
 * Copyright (C) 2008 Philippine Business Solution Developers <http://www.philbsd.com>
 * All rights reserved.
 *
 *
 * LICENSE
 *
 * This source file is subject to the GNU General Public License v3 license
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://www.gnu.org/licenses/gpl-3.0.txt
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@philbsd.com so we can send you a copy immediately.
 *
 * @copyright  (c) 2008 Philippine Business Solution Developers. (http://www.philbsd.com)
 * @license    http://www.gnu.org/licenses/gpl-3.0.txt
 * @version    $Id: loans.php 244 2008-05-01 16:39:50Z jundie2000 $
 */

if (!defined('BASEPATH')) exit('No direct script access allowed');

class Loans extends Model
{
	public function getAll()
	{
		$ret = array();
		// get all entries from loans table (it can be limit down to number of records to retrieve)
		$this->db->select('*')->from('loans');
		if ($loanQuery = $this->db->get()) {
			foreach ($loanQuery->result_array() as $row) {
				// get borrower information
				$this->db->select('*')->from('borrowers')
						 ->where('id', $row['borrower_id']);
				if ($borrowerQuery = $this->db->get()) {
					$borrower = $borrowerQuery->row_array();
					// get profile information
					$this->db->select('*')->from('profiles')
						 ->where('id', $borrower['profile_id']);
					if ($profileQuery = $this->db->get()) {
						$profile = $profileQuery->row_array();
						$borrower['Profile'] = $profile;
					}
					$row['Borrower'] = $borrower;
				}
				// get co-borrower information
				$this->db->select('*')->from('borrowers')
						 ->where('id', $row['coborrower_id']);
				if ($coBorrowerQuery = $this->db->get()) {
					$coBorrower = $coBorrowerQuery->row_array();
					// get profile information
					$this->db->select('*')->from('profiles')
						 ->where('id', $coBorrower['profile_id']);
					if ($profileQuery = $this->db->get()) {
						$profile = $profileQuery->row_array();
						$coBorrower['Profile'] = $profile;
					}
					$row['CoBorrower'] = $coBorrower;
				}
				$ret[] = $row;
			}
		}
		return $ret;
	}

    public function getLoanIndex()
	{
		$ret = array();
        $this->db->select(' loan.borrower_id, loan.id,
                            bo.profile_id,
                            prof.first_name, prof.last_name,
                         ')
                    ->from('loans loan')
                    ->join('borrowers bo', 'bo.id = loan.borrower_id', 'left')
                    ->join('profiles prof', 'prof.id = bo.profile_id', 'left');
        if ($loanQuery = $this->db->get()) {
            foreach ($loanQuery->result_array() as $row) {
                $ret[] = $row;
            }
        }

		return $ret;
	}

	public function getLoan($id)
	{
        $ret = array();
        $this->db->select(' loan.*,
                            bo.profile_id,
                            prof.first_name,
                            prof.last_name
                         ')
                    ->from('loans loan')
                    ->join('borrowers bo', 'bo.id = loan.borrower_id', 'left')
                    ->join('profiles prof', 'prof.id = bo.profile_id', 'left')
                    ->where('loan.id', $id);
        if ($loanQuery = $this->db->get()) {
            $loan = $loanQuery->row_array();
        }

        $loan['total_paid'] = 0;
        // get payments information
		$this->db->select('*')->from('payments')
					 ->where('loan_id', $id);
        if ($paymentQuery = $this->db->get()) {
            $ctr = 0;
            foreach ($paymentQuery->result_array() as $row) {
                $loan['Payment'][] = $row;
                $loan['total_paid'] += $loan['Payment'][$ctr]['amount_paid'];
                $ctr++;
            }
        }

		return $loan;
	}

    public function editLoan($id)
	{
        $ret = array();
        $this->db->select(' loan.*,
                            bo.profile_id,
                            prof.first_name,
                            prof.last_name
                         ')
                    ->from('loans loan')
                    ->join('borrowers bo', 'bo.id = loan.borrower_id', 'left')
                    ->join('profiles prof', 'prof.id = bo.profile_id', 'left')
                    ->where('loan.id', $id);
        if ($loanQuery = $this->db->get()) {
            $loan = $loanQuery->row_array();
        }

		return $loan;
	}

    public function getPayment($id)
	{
        $ret = array();
        $this->db->select(' pay.*,
                            loan.borrower_id,
                            bo.profile_id,
                            prof.first_name, prof.last_name
                         ')
                    ->from('payments pay')
                    ->join('loans loan', 'loan.id = pay.loan_id', 'left')
                    ->join('borrowers bo', 'bo.id = loan.borrower_id', 'left')
                    ->join('profiles prof', 'prof.id = bo.profile_id', 'left')
                    ->where('pay.id', $id);
        if ($paymentQuery = $this->db->get()) {
            $payment = $paymentQuery->row_array();
        }
		$ret = $payment;

		return $ret;
	}
     public function getPaymentToday()
	{
      $ret = array();
        $this->db->select('*')
                    ->from('payments')
                    ->where('due_date', date("Y-m-d"));
        if ($paymentQuery = $this->db->get()) {
            foreach ($paymentQuery->result_array() as $row) {
                $this->db->select(' loan.collector_id, loan.borrower_id,
                                    bo.profile_id,
                                    prof.first_name, prof.last_name
                                  ')
                        ->from('loans loan')
                        ->join('borrowers bo', 'bo.id = loan.borrower_id', 'left')
                        ->join('profiles prof', 'prof.id = bo.profile_id', 'left')
                        ->where('loan.id', $row['loan_id']);
                if ($loanQuery = $this->db->get()) {
                    $loan = $loanQuery->row_array();
                    $row['info'] = $loan;
                }
                $ret[] = $row;
            }
        }

		return $ret;
	}

    public function getBorrower($id)
	{

        $ret = array();
        $this->db->select(' loan.borrower_id, loan.id,
                            bo.status_date, bo.status_id, bo.profile_id,
                            prof.*
                         ')
                    ->from('loans loan')
                    ->join('borrowers bo', 'bo.id = loan.borrower_id', 'left')
                    ->join('profiles prof', 'prof.id = bo.profile_id', 'left')
                    ->where('loan.id', $id);
        if ($loanQuery = $this->db->get()) {
            $loan = $loanQuery->row_array();
        }
        $loan['loan_id'] = $id;
		$ret = $loan;
		return $ret;
	}

    public function getCoBorrower($id)
	{

        $loan = array();
        $this->db->select(' loan.coborrower_id, loan.borrower_id, loan.id,
                            cbo.*,
                            prof.*
                         ')
                    ->from('loans loan')
                    ->join('borrowers cbo', 'cbo.id = loan.coborrower_id', 'left')
                    ->join('profiles prof', 'prof.id = cbo.profile_id', 'left')
                    ->where('loan.id', $id);
        if ($loanQuery = $this->db->get()) {
            $loan = $loanQuery->row_array();
        }

        $this->db->select(' loan.borrower_id,
                            bo.profile_id,
                            prof.last_name, prof.first_name
                         ')
                    ->from('loans loan')
                    ->join('borrowers bo', 'bo.id = loan.borrower_id', 'left')
                    ->join('profiles prof', 'prof.id = bo.profile_id', 'left')
                    ->where('loan.id', $id);
        if ($borrowerQuery = $this->db->get()) {
            $borrower = $borrowerQuery->row_array();
        }
        $loan['Borrower'] = $borrower;
        $loan['loan_id'] = $id;

		return $loan;
	}

    public function getBusinessIndex($id)
	{
        $ret = array();
        $this->db->select(' loan.borrower_id, loan.id,
                            bo.profile_id,
                            prof.first_name,
                            prof.last_name
                         ')
                    ->from('loans loan')
                    ->join('borrowers bo', 'bo.id = loan.borrower_id', 'left')
                    ->join('profiles prof', 'prof.id = bo.profile_id', 'left')
                    ->where('loan.id', $id);
        if ($loanQuery = $this->db->get()) {
            $loan = $loanQuery->row_array();
        }

        // get business information
		$this->db->select('*')->from('businesses')
					 ->where('borrower_id', $loan['borrower_id']);
        if ($businessQuery = $this->db->get()) {
            $ctr = 0;
            foreach ($businessQuery->result_array() as $row) {
                $loan['Business'][] = $row;
                $ctr++;
            }
        }

		return $loan;
	}

    public function editBusiness($id)
	{
        $ret = array();
        $this->db->select(' bus.*,
                            loan.id,
                            bo.profile_id,
                            prof.first_name, prof.last_name
                         ')
                    ->from('businesses bus')
                    ->join('loans loan', 'loan.borrower_id = bus.borrower_id', 'left')
                    ->join('borrowers bo', 'bo.id = loan.borrower_id', 'left')
                    ->join('profiles prof', 'prof.id = bo.profile_id', 'left')
                    ->where('bus.id', $id);
        if ($businessQuery = $this->db->get()) {
            $business = $businessQuery->row_array();
        }
        $business['bus_id'] = $id;
		return $business;
	}

    public function getNotes($id)
	{
		$ret = array();
        // get all entries from loans table (it can be limit down to number of records to retrieve)
		$this->db->select('borrower_id')->from('loans')
				 ->where('id', $id);
		if ($loanQuery = $this->db->get()) {
			$loan = $loanQuery->row_array();

            // get all entries from notes table (it can be limit down to number of records to retrieve)
		    $this->db->select('*')->from('notes')
				 ->where('borrower_id', $loan['borrower_id'])
                 ->order_by("note_date", "desc");
		    if ($noteQuery = $this->db->get()) {
               	foreach ($noteQuery->result_array() as $row) {
                   	$loan['Notes'][] = $row;
               	}
			}

            // get all entries from borrowers table (it can be limit down to number of records to retrieve)
			$this->db->select('id,profile_id')->from('borrowers')
				 ->where('id', $loan['borrower_id']);
			if ($borrowerQuery = $this->db->get()) {
				$borrower = $borrowerQuery->row_array();

            // get all entries from Profile table (it can be limit down to number of records to retrieve)
		    	$this->db->select('last_name,first_name')->from('profiles')
					 ->where('id', $borrower['profile_id']);
		    	if ($profileQuery = $this->db->get()) {
			   		$profile = $profileQuery->row_array();
			   		$borrower['Profile'] = $profile;
				}

				$loan['Borrower'] = $borrower;
   			}
        	$ret = $loan;
		}
		return $ret;
	}

	public function editNotes($id)
	{
		$ret = array();
        // get all entries from notes table (it can be limit down to number of records to retrieve)
		$this->db->select('*')->from('notes')
			->where('id', $id);
		if ($noteQuery = $this->db->get()) {
        	$note = $noteQuery->row_array();
		}

        // get all entries from loans table (it can be limit down to number of records to retrieve)
		$this->db->select('id')->from('loans')
			->where('borrower_id', $note['borrower_id']);
		if ($loanQuery = $this->db->get()) {
			$note['loan'] = $loanQuery->row_array();

			$this->db->select('profile_id')->from('borrowers')
				 ->where('id', $note['borrower_id']);
			if ($borrowerQuery = $this->db->get()) {
				$borrower = $borrowerQuery->row_array();

				// get profile information
				$this->db->select('last_name,first_name')->from('profiles')
					 ->where('id', $borrower['profile_id']);
				if ($profileQuery = $this->db->get()) {
				  	$profile = $profileQuery->row_array();
					$borrower['Profile'] = $profile;
				}
				$note['Borrower'] = $borrower;
            }
        	$ret = $note;
		}
		return $ret;
	}

    public function getCollateral($id)
	{
		$ret = array();
        // get entries from loans table (it can be limit down to number of records to retrieve)
		$this->db->select('borrower_id,id')->from('loans')
				 ->where('id', $id);
		if ($loanQuery = $this->db->get()) {
			$loan = $loanQuery->row_array();

			// get all entries from borrowers table (it can be limit down to number of records to retrieve)
			$this->db->select('id,profile_id')->from('borrowers')
				 ->where('id', $loan['borrower_id']);
			if ($borrowerQuery = $this->db->get()) {
				$borrower = $borrowerQuery->row_array();

            	// get all entries from Profile table (it can be limit down to number of records to retrieve)
		    	$this->db->select('*')->from('collaterals')
					 ->where('borrower_id', $borrower['id']);
		    	if ($collateralQuery = $this->db->get()) {
                	foreach ($collateralQuery->result_array() as $row) {
                    	$loan['Collateral'][] = $row;
                	}
				}
                // get all entries from Profile table (it can be limit down to number of records to retrieve)
		    	$this->db->select('last_name,first_name')->from('profiles')
					 ->where('id', $borrower['profile_id']);
		    	if ($profileQuery = $this->db->get()) {
			   		$profile = $profileQuery->row_array();
			   		$borrower['Profile'] = $profile;
				}

				$loan['Borrower'] = $borrower;
        	}

        	$ret = $loan;
		}
		return $ret;
	}

    public function editCollateral($id)
	{
		$ret = array();
        // get all entries from notes table (it can be limit down to number of records to retrieve)
		$this->db->select('*')->from('collaterals')
			->where('id', $id);
		if ($collateralQuery = $this->db->get()) {
        	$collateral = $collateralQuery->row_array();
		}

        // get all entries from loans table (it can be limit down to number of records to retrieve)
		$this->db->select('id')->from('loans')
			->where('borrower_id', $collateral['borrower_id']);
		if ($loanQuery = $this->db->get()) {
			$collateral['loan'] = $loanQuery->row_array();

			$this->db->select('profile_id')->from('borrowers')
				 ->where('id', $collateral['borrower_id']);
			if ($borrowerQuery = $this->db->get()) {
				$borrower = $borrowerQuery->row_array();

				// get profile information
				$this->db->select('last_name,first_name')->from('profiles')
					 ->where('id', $borrower['profile_id']);
				if ($profileQuery = $this->db->get()) {
				  	$profile = $profileQuery->row_array();
					$borrower['Profile'] = $profile;
				}
				$collateral['Borrower'] = $borrower;
            }
        	$ret = $collateral;
		}
		return $ret;
	}

}